import json
from flask import Flask,render_template,request,redirect,url_for
import sqlite3


app = Flask(__name__)

@app.route('/',endpoint='index')
def hello_world():  # put application's code here
    return render_template("index.html")

@app.route('/index')
def home():
    return  render_template("index.html")

@app.route('/college')
def college():
    datalist=[]
    con=sqlite3.connect("college.db")
    cur=con.cursor()
    sql='''
        select row_number() over (),* 
        from (select * from college where title like "%计算机%" order by date desc)
    '''
    data=cur.execute(sql)
    for item in data:
        datalist.append(item)
    cur.close()
    con.close()
    return render_template("college.html",colleges=datalist)

@app.route('/college_humanity')
def college_humanity():
    datalist=[]
    con=sqlite3.connect("college.db")
    cur=con.cursor()
    sql='''
        select row_number() over (),* from(
    select * from (select * from college where title like "%经管%") union
    select * from (select * from college where title like "%经济%") order by date desc )

    '''
    data=cur.execute(sql)
    for item in data:
        datalist.append(item)
    cur.close()
    con.close()
    return render_template("college_humanity.html",colleges=datalist)

@app.route('/college_all')
def college_all():
    datalist=[]
    con=sqlite3.connect("college.db")
    cur=con.cursor()
    sql='''
        select row_number() over (),* from 
            (select * from college order by date desc);
    '''
    data=cur.execute(sql)
    for item in data:
        datalist.append(item)
    cur.close()
    con.close()
    return render_template("college_all.html",colleges=datalist)

@app.route('/traffic')
def traffic():
    college_name=["农学与资源环境学院","园艺园林学院","动物科学与动物医学学院","水产学院",
              "经济管理学院","食品科学与生物工程学院","工程技术学院","计算机与信息工程学院",
              "人文学院","水利工程学院","基础科学学院","马克思主义学院"]
    college_num=[88,68,80,67,38,58,20,53,57,66,63,36]

#region    数据库查询
    # college_traffic= {"农学与资源环境学院":0,"园艺园林学院":0,"动物科学与动物医学学院":0,"水产学院":0,
    #           "经济管理学院":0,"食品科学与生物工程学院":0,"工程技术学院":0,"计算机与信息工程学院":0,
    #           "人文学院":0,"水利工程学院":0,"基础科学学院":0,"马克思主义学院":0,
    #           "体育教育部学院":0,"职业技术学院":0}
    # con=sqlite3.connect("college.db")
    # cur=con.cursor()
    # sql_agriculture='''
    #     select row_number() over (),* from(
    #         select * from (select * from college where title like "%农学%") union
    #         select * from (select * from college where title like "%资源%") order by date desc )
    # '''
    # sql_garden='''
    #     select row_number() over (),* from(
    #         select * from (select * from college where title like "%园艺%") union
    #         select * from (select * from college where title like "%园林%") order by date desc )
    # '''
    # sql_animal='''
    #     select row_number() over (),* from(
    #         select * from (select * from college where title like "%动物%") union
    #         select * from (select * from college where title like "%动科%") union
    #         select * from (select * from college where title like "%动医%") order by date desc )
    # '''
    # sql_fisher='''
    #     select row_number() over (),* from
    #         (select * from college where title like "%水产%" order by date desc)
    # '''
    # sql_economic='''
    #     select row_number() over (),* from(
    #     select * from (select * from college where title like "%经济%") union
    #     select * from (select * from college where title like "%经管%") order by date desc )
    # '''
    # sql_food='''
    #     select row_number() over (),* from
    #         (select * from college where title like "%食品%" order by date desc)
    # '''
    # sql_engineer='''
    #     select row_number() over (),* from
    #         (select * from college where title like "%工程技术%" order by date desc)
    # '''
    # sql_computer='''
    #     select row_number() over (),* from
    #         (select * from college where title like "%计算机%" order by date desc)
    # '''
    # sql_humanity='''
    #     select row_number() over (),* from
    #         (select * from college where title like "%人文%" order by date desc)
    # '''
    # sql_water='''
    #     select row_number() over (),* from
    #         (select * from college where title like "%水利%" order by date desc)
    # '''
    # sql_basics='''
    #     select row_number() over (),* from
    #         (select * from college where title like "%基础科学%" order by date desc)
    # '''
    # sql_Marx='''
    #     select row_number() over (),* from
    #         (select * from college where title like "%基础科学%" order by date desc)
    # '''
    # traffic_num=cur.execute(sql)
    # college_traffic["农学与资源环境学院"]=traffic_num
    # cur.close()
    # con.close()
    #
    # sql="select score,count(score) from movie250 group by score"
    # data=cur.execute(sql)
    # for item in data:
    #     score.append(str(item[0]))
    #     num.append(item[1])
    # cur.close()
    # con.close()
    # return render_template("traffic.html",score=score,num=num)
#endregion

    # return render_template("traffic.html")
    return render_template("traffic.html",names=college_name,nums=college_num)

@app.route('/word')
def word():
    return render_template("word.html")

@app.route('/team')
def team():
    return render_template("team.html")

@app.route('/register',methods=['GET','POST'])
def register():
    errorMessage= '用户注册必需填写邀请码'
    if request.method=='POST':#若该网站当前走的是POSt请求
        name=request.form.get('name')
        email=request.form.get('email')
        password=request.form.get('password')
        message=request.form.get('message')

        # if message!=None:     #用户输入邀请码，执行注册功能
        if len(message)!=0:
            conn = sqlite3.connect("student.db")
            cur = conn.cursor()
            entity = (message,)
            cur.execute('select * from student where message=(?)', entity)
            result = cur.fetchall()  # 接受数据库的查询结果
            if not bool(result):  # 若数据库查询返回空
                conn.commit()
                conn.close()
                errorMessage = '邀请码无效'
                return render_template("register.html", errorMessage=errorMessage)
            else:  # 邀请码有效，则注册学生信息
                try:
                    entity = (name,str(name),email,password,'')
                    cur.execute('insert into student values(?,?,?,?,?)', entity)
                except Exception as e:
                    errorMessage = '该账号已存在，请重新登录'
                    return render_template("register.html", errorMessage=errorMessage)
                conn.commit()
                conn.close()
                errorMessage = '注册成功，请重新登录'
                return render_template("register.html", errorMessage=errorMessage)

        entity = (name, password)
        conn = sqlite3.connect("student.db")
        cur = conn.cursor()
        cur.execute('select * from student where id=(?) and password=(?)', entity)
        result = cur.fetchall()  # 接受数据库的查询结果
        # print(result if bool(result) else 'error')
        conn.commit()
        conn.close()
        if not bool(result):        #若数据库查询返回空
            errorMessage= '用户名或密码错误！'
            return render_template("register.html",errorMessage=errorMessage)
        else:       #学号和密码相匹配
            # j_str=json.dump(users)        #转成文字
            return redirect(url_for('index'))       #redirect:重定向，状态码：302   url_for:配合endpoint，实现路由反向解析

    return  render_template("register.html",errorMessage=errorMessage)        #直接输入网址 走的是GET请求

@app.route('/service')
def service():
    return  render_template("service.html")

@app.route('/info',methods=['GET','POST'])
def info():
    errorMassage=''
    con=sqlite3.connect("student.db")
    cur=con.cursor()

    if request.method=='POST':      #若该网站当前走的是POST请求
        id=request.form.get('id')
        try:
            entity = (id,)
            cur.execute('delete from student where id =(?)', entity)
            con.commit()
            cur.execute('select * from student')
            results = cur.fetchall()
            cur.close()
            con.close()
            return render_template("info.html", infos=results, errorMassage='用户'+id+'删除成功')
        except Exception as e:
            cur.close()
            con.close()
            return render_template("info.html", infos=results, errorMassage='用户' + id + '无法删除')

    cur.execute('select * from student')        #GET请求路线
    results=cur.fetchall()
    cur.close()
    con.close()
    return  render_template("info.html",infos=results,errorMassage=errorMassage)

@app.route('/info_add',methods=['GET','POST'])
def info_add():
    if request.method=='POST':#若该网站当前走的是POSt请求
        id=request.form.get('id')
        name = request.form.get('name')
        email=request.form.get('email')
        password=request.form.get('password')

        conn = sqlite3.connect("student.db")
        cur = conn.cursor()
        try:
            entity = (int(id),name,email,password,'')
            cur.execute('insert into student values(?,?,?,?,?)', entity)
            conn.commit()
            errorMassage = '添加用户成功'
            cur.execute('select * from student')  # GET请求路线
            results = cur.fetchall()
            cur.close()
            conn.close()
            return render_template("info.html", infos=results, errorMassage=errorMassage)
        except Exception as e:
            cur.close()
            conn.close()
            errorMassage = '该账号已存在，请重新注册'
            return render_template("info_add.html", errorMessage=errorMassage)

    return  render_template("info_add.html")        #GET请求

@app.route('/info_modify',methods=['GET','POST'])
def info_modify():
    if request.method=='POST':#若该网站当前走的是POST请求
        id=request.form.get('id')

        con = sqlite3.connect("student.db")
        cur = con.cursor()
        try:
            entity=(id,)
            cur.execute('select * from student where id=(?)',entity)
            results = cur.fetchall()
            cur.close()
            con.close()
            return render_template("info_m_add.html",results=results)
        except Exception as e:
            cur.close()
            con.close()
            return render_template("info.html")

    con=sqlite3.connect("student.db")
    cur=con.cursor()
    cur.execute('select * from student')
    results=cur.fetchall()
    cur.close()
    con.close()
    return  render_template("info_modify.html",infos=results)

@app.route('/info_m_add',methods=['GET','POST'])
def info_m_add():
    if request.method == 'POST':  # 若该网站当前走的是POST请求
        id = request.form.get('id')
        try:
            entity = (id,)
            con = sqlite3.connect("student.db")
            cur = con.cursor()
            cur.execute('select * from student where id=(?)', entity)
            results = cur.fetchall()
            cur.close()
            con.close()
            return render_template("info_m_add.html", results=results)
        except Exception as e:
            cur.close()
            con.close()
            return render_template("info_m_add.html", errorMassage='用户' + id + '有误，无法修改')

    return render_template("info_m_add.html")

@app.route('/info_m_a_finish',methods=['GET','POST'])
def info_m_a_finish():
    if request.method == 'POST':  # 若该网站当前走的是POSt请求
        id = request.form.get('id')
        name = request.form.get('name')
        email = request.form.get('email')
        password = request.form.get('password')
        invite=request.form.get('invite')

        conn = sqlite3.connect("student.db")
        cur = conn.cursor()
        try:
            #先根据id把该用户删了
            entity = (id,)
            cur.execute('delete from student where id =(?)', entity)
            conn.commit()
            #再新建一个该用户
            entity = (int(id), name, email, password, invite)
            cur.execute('insert into student values(?,?,?,?,?)', entity)
            conn.commit()
            #返回全部的用户信息
            errorMassage = '用户修改完成'
            cur.execute('select * from student')  # GET请求路线
            results = cur.fetchall()
            cur.close()
            conn.close()
            return render_template("info.html", infos=results, errorMassage=errorMassage)
        except Exception as e:
            cur.execute('select * from student')  # GET请求路线
            results = cur.fetchall()
            cur.close()
            conn.close()
            errorMassage = '用户修改失败'
            return render_template("info.html", infos=results,errorMessage=errorMassage)

    return render_template("info.html")

if __name__ == '__main__':
    # print(app.url_map)
    app.run(debug=True)
